package com.projectsexception.festivales.setup;

import java.io.Serializable;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.projectsexception.festivales.db.CacheObject;
import com.projectsexception.festivales.db.DBHelper;
import com.projectsexception.festivales.favorites.model.Favorite;
import com.projectsexception.festivales.news.model.Feed;
import com.projectsexception.festivales.util.CustomLog;
import com.projectsexception.festivales.util.Serializator;

public final class ProjectResources {
    
    private static final String FEED_CACHE_TYPE = "feedCache";

    private static ProjectResources myResources;
    
    private List<MapInfo> maps;
    private List<FeedInfo> feeds;
    private DBHelper dBHelper;
    private SimpleDateFormat dateFormat;
    
    public static ProjectResources getInstance(Context context) {
        if (myResources == null) {
            myResources = new ProjectResources(context);
        }
        return myResources;
    }
        
    private ProjectResources(Context context) {
        dBHelper = new DBHelper(context);
        dateFormat = new SimpleDateFormat("dd-MM-yyyy");
    }
    
    public List<MapInfo> getMaps() {
        if (maps == null) {
            loadMaps();
        }
        return maps;
    }

    private void loadMaps() {
        CustomLog.debug("ProjectResources", "loadMaps");
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.MAPS_TABLE, 
                new String[] {DBHelper.COL_ID, DBHelper.COL_NAME, DBHelper.COL_LINK}, 
                null, null, null, null, null);  
        if (c != null) {
            maps = new ArrayList<MapInfo>();
            MapInfo mapInfo;
            while (c.moveToNext()) {
                mapInfo = new MapInfo();
                mapInfo.setId(c.getInt(0));
                mapInfo.setName(c.getString(1));                
                mapInfo.setImageURL(c.getString(2));
                maps.add(mapInfo);
            }
        }
        closeResources(c);
    }
    
    public List<FeedInfo> getFeeds() {
        if (feeds == null) {
            loadFeeds();
        }
        return feeds;
    }

    private void loadFeeds() {
        CustomLog.debug("ProjectResources", "loadFeed");
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.FEEDS_TABLE, 
                new String[] {DBHelper.COL_ID, DBHelper.COL_NAME, DBHelper.COL_LINK}, 
                null, null, null, null, null);  
        if (c != null) {
            feeds = new ArrayList<FeedInfo>();
            FeedInfo feedInfo;
            while (c.moveToNext()) {
                feedInfo = new FeedInfo();
                feedInfo.setId(c.getInt(0));
                feedInfo.setName(c.getString(1));                
                feedInfo.setLink(c.getString(2));
                feeds.add(feedInfo);
            }
        }
        closeResources(c);
    }
    
    public List<Date> getShowDates() {
        List<Date> showDates = new ArrayList<Date>();
        CustomLog.debug("ProjectResources", "getShowDates");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        Cursor c = db.query(true, DBHelper.SHOWS_TABLE, 
                new String[] {DBHelper.COL_DATE}, 
                null, null, null, null, null, null);
        Date time;
        if (c != null) {
            while (c.moveToNext()) {
                try {
                    time = dateFormat.parse(c.getString(0));
                } catch (ParseException e) {
                    time = new Date(0);
                    CustomLog.error("ProjectResources", e.getMessage());
                }                
                showDates.add(time);
            }
        }
        closeResources(c);
        return showDates;
    }
    
    public List<ShowInfo> getShowsInDate(Date date) {
        CustomLog.debug("ProjectResources", "getShowsInDate");
        List<ShowInfo> shows = new ArrayList<ShowInfo>();
        String time = dateFormat.format(date);
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.SHOWS_TABLE, 
                new String[] {DBHelper.COL_ID, DBHelper.COL_DATE, 
                DBHelper.COL_START_TIME, DBHelper.COL_END_TIME, 
                DBHelper.COL_BAND_ID, DBHelper.COL_STAGE_ID}, 
                DBHelper.COL_DATE + " = ?", new String[] {time}, 
                null, null, null);  
        if (c != null) {
            while (c.moveToNext()) {
                shows.add(populateShow(c));
            }
        }
        closeResources(c);
        return shows;
    }
    
    public ShowInfo getShow(Integer id) {
        ShowInfo show = null;
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.SHOWS_TABLE, 
                new String[] { DBHelper.COL_ID, DBHelper.COL_DATE, 
                DBHelper.COL_START_TIME, DBHelper.COL_END_TIME, 
                DBHelper.COL_BAND_ID, DBHelper.COL_STAGE_ID }, 
                DBHelper.COL_ID + " = ?", new String[] {Integer.toString(id)}, 
                null, null, null);  
        if (c != null) {
            if (c.moveToNext()) {
                show = populateShow(c);
            }
        }
        closeResources(c);
        return show;
    }
    
    public StageInfo getStage(Integer id) {
        StageInfo stage = null;
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.STAGES_TABLE, 
                new String[] { DBHelper.COL_NAME }, 
                DBHelper.COL_ID + " = ?", new String[] {Integer.toString(id)}, 
                null, null, null);  
        if (c != null) {
            if (c.moveToNext()) {
                stage = new StageInfo();
                stage.setId(id);
                stage.setName(c.getString(0));
            }
        }
        closeResources(c);
        return stage;
    }
    
    public BandInfo getBand(Integer id) {
        BandInfo band = null;
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.BANDS_TABLE, 
                new String[] { DBHelper.COL_NAME, DBHelper.COL_LINK, DBHelper.COL_BIOGRAPHY }, 
                DBHelper.COL_ID + " = ?", new String[] {Integer.toString(id)}, 
                null, null, null);  
        if (c != null) {
            if (c.moveToNext()) {
                band = new BandInfo();
                band.setId(id);
                band.setName(c.getString(0));
                band.setPhotoURL(c.getString(1));
                band.setBiography(c.getString(2));
                band.setOfficialChannels(getOfficialChannels(id));
            }
        }
        closeResources(c);
        return band;
    }
    
    public List<OfficialChannelInfo> getOfficialChannels(Integer bandId) {
        CustomLog.debug("ProjectResources", "getShowsInDate");
        List<OfficialChannelInfo> channels = new ArrayList<OfficialChannelInfo>();
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.OFFICIAL_CHANNELS_TABLE, 
                new String[] {DBHelper.COL_ID, DBHelper.COL_TYPE, DBHelper.COL_LINK}, 
                DBHelper.COL_BAND_ID + " = ?", new String[] {bandId.toString()}, 
                null, null, null);  
        if (c != null) {
            OfficialChannelInfo channel;
            while (c.moveToNext()) {
                channel = new OfficialChannelInfo();
                channel.setId(c.getInt(0));
                channel.setType(Channel.valueOf(c.getString(1)));
                channel.setLink(c.getString(2));
                channels.add(channel);
            }
        }
        closeResources(c);
        return channels;
    }
    
    public void insertMaps(List<MapInfo> maps) {
        CustomLog.debug("ProjectResources", "insertMaps");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (maps != null) {
            db.delete(DBHelper.MAPS_TABLE, null, null);
            ContentValues values = new ContentValues();
            int id;
            for (MapInfo map : maps) {
                values.clear();
                values.put(DBHelper.COL_NAME, map.getName());
                values.put(DBHelper.COL_LINK, map.getImageURL());
                id = (int) db.insert(DBHelper.MAPS_TABLE, null, values);
                map.setId(id);
            }
        }
    }
    
    public void insertFeeds(List<FeedInfo> feeds) {
        CustomLog.debug("ProjectResources", "insertFeeds");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (feeds != null) {
            db.delete(DBHelper.FEEDS_TABLE, null, null);
            ContentValues values = new ContentValues();
            int id;
            for (FeedInfo feed : feeds) {
                values.clear();
                values.put(DBHelper.COL_NAME, feed.getName());
                values.put(DBHelper.COL_LINK, feed.getLink());
                id = (int) db.insert(DBHelper.FEEDS_TABLE, null, values);
                feed.setId(id);
            }
        }
    }
    
    public void saveFeedCache(Feed feed) {
        saveCache(FEED_CACHE_TYPE, Integer.toString(feed.getFeedInfoId()), feed);
    }
    
    public Feed loadFeedCache(Integer actualFeed) {
        return (Feed) getCache(FEED_CACHE_TYPE, actualFeed.toString()).getObject();
    }
    
    public void deleteAllFeedCache() {
        deleteCache(FEED_CACHE_TYPE);
    }

    public void insertShows(List<ShowInfo> shows) {
        CustomLog.debug("ProjectResources", "updateShows");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (shows != null) {
            ContentValues values = new ContentValues();
            Map<Integer, StageInfo> stages = new HashMap<Integer, StageInfo>();
            Map<Integer, BandInfo> bands = new HashMap<Integer, BandInfo>();
            for (ShowInfo show : shows) {
                values.clear();
                values.put(DBHelper.COL_DATE, dateFormat.format(show.getDate()));
                values.put(DBHelper.COL_START_TIME, show.getStartTime());
                values.put(DBHelper.COL_END_TIME, show.getEndTime());
                values.put(DBHelper.COL_BAND_ID, show.getBand().getId());
                values.put(DBHelper.COL_STAGE_ID, show.getStage().getId());
                db.insert(DBHelper.SHOWS_TABLE, null, values);
                stages.put(show.getStage().getId(), show.getStage());
                bands.put(show.getBand().getId(), show.getBand());
            }
            insertStages(stages.values());
            insertBands(bands.values());
        }
    }
    
    public void insertStages(Collection<StageInfo> stages) {
        CustomLog.debug("ProjectResources", "insertStages");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (stages != null) {
            ContentValues values = new ContentValues();
            for (StageInfo stage : stages) {
                values.clear();
                values.put(DBHelper.COL_NAME, stage.getName());
                values.put(DBHelper.COL_ID, stage.getId());
                db.insert(DBHelper.STAGES_TABLE, null, values);
            }
        }
    }
    
    public void insertBands(Collection<BandInfo> bands) {
        CustomLog.debug("ProjectResources", "insertBands");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (bands != null) {
            ContentValues values = new ContentValues();
            for (BandInfo band : bands) {
                values.clear();
                values.put(DBHelper.COL_ID, band.getId());
                values.put(DBHelper.COL_NAME, band.getName());
                values.put(DBHelper.COL_LINK, band.getPhotoURL());
                values.put(DBHelper.COL_BIOGRAPHY, band.getBiography());
                db.insert(DBHelper.BANDS_TABLE, null, values);
                db.delete(DBHelper.OFFICIAL_CHANNELS_TABLE, DBHelper.COL_BAND_ID + " = ?", 
                        new String[] {Integer.toString(band.getId())});
                insertOfficialChannels(band.getId(), band.getOfficialChannels());
            }
        }
    }
    
    public void insertOfficialChannels(int bandId, Collection<OfficialChannelInfo> channels) {
        CustomLog.debug("ProjectResources", "insertOfficialChannels");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (channels != null) {
            ContentValues values = new ContentValues();
            for (OfficialChannelInfo channel : channels) {
                values.clear();
                values.put(DBHelper.COL_BAND_ID, bandId);
                values.put(DBHelper.COL_LINK, channel.getLink());
                values.put(DBHelper.COL_TYPE, channel.getType().name());
                db.insert(DBHelper.OFFICIAL_CHANNELS_TABLE, null, values);                
            }
        }
    }

    private ShowInfo populateShow(Cursor c) {
        Date showDate;
        ShowInfo showInfo = new ShowInfo();
        showInfo.setId(c.getInt(0));
        try {
            showDate = dateFormat.parse(c.getString(1));
        } catch (ParseException e) {
            showDate = new Date(0);
            CustomLog.error("ProjectResources", e.getMessage());
        }
        showInfo.setDate(showDate);
        showInfo.setStartTime(c.getString(2));
        showInfo.setEndTime(c.getString(3));
        showInfo.setBand(getBand(c.getInt(4)));
        showInfo.setStage(getStage(c.getInt(5)));
        return showInfo;
    }
    
    public List<Favorite> getFavorites() {
        List<Favorite> result = new ArrayList<Favorite>();
        CustomLog.debug("ProjectResources", "getFavorites");
        SQLiteDatabase db = dBHelper.getWritableDatabase();        
        Cursor c = db.query(DBHelper.FAVORITES_TABLE, 
                new String[] {DBHelper.COL_ID, DBHelper.COL_SHOW_ID}, 
                null, null, null, null, null);  
        if (c != null) {
            Favorite fav;
            while (c.moveToNext()) {
                fav = new Favorite();
                fav.setId(c.getInt(0));
                fav.setShowInfo(getShow(c.getInt(1)));
                result.add(fav);
            }
        }
        closeResources(c);
        return result;
    }
    
    public void saveFavorite(Favorite favorite) {
        CustomLog.debug("ProjectResources", "saveFavorite");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        ContentValues initialValues = new ContentValues();
        initialValues.put(DBHelper.COL_SHOW_ID, favorite.getShowInfo().getId());
        if (favorite.getId() == 0) {
            db.insert(DBHelper.FAVORITES_TABLE, null, initialValues);
        } else {
            db.update(DBHelper.FAVORITES_TABLE, initialValues, 
                    DBHelper.COL_ID + " = ?", new String[] {Integer.toString(favorite.getId())});
        }
    }
    
    public void deleteFavorite(Favorite favorite) {
        CustomLog.debug("ProjectResources", "deleteFavorite");
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        db.delete(DBHelper.FAVORITES_TABLE, 
                DBHelper.COL_ID + " = ?", new String[] {Integer.toString(favorite.getId())});
    }
    
    private void saveCache(String type, String code, Object object) {
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        if (object != null) {
            db.delete(DBHelper.CACHE_TABLE,
                    DBHelper.COL_TYPE + " = ? AND " + DBHelper.COL_CODE + " = ?", 
                    new String[] { type, code });
            if (Serializable.class.isAssignableFrom(object.getClass())) {
                byte[] array = Serializator.exportObject((Serializable) object);
                if (array != null) {
                    ContentValues values = new ContentValues();
                    values.put(DBHelper.COL_TYPE, type);
                    values.put(DBHelper.COL_CODE, code);
                    values.put(DBHelper.COL_UPDATED, new Date().getTime());
                    values.put(DBHelper.COL_DATA, array);
                    db.insert(DBHelper.CACHE_TABLE, null, values);
                }
            }
            
        }
    }
    
    private CacheObject getCache(String type, String code) {
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        Cursor c = db.query(DBHelper.CACHE_TABLE, 
                new String[] { DBHelper.COL_UPDATED, DBHelper.COL_DATA }, 
                DBHelper.COL_TYPE + " = ? AND " + DBHelper.COL_CODE + " = ?", 
                new String[] { type, code }, 
                null, null, null);
        Object o = null;
        long updated = 0;
        if (c != null && c.moveToNext()) {
            updated = c.getLong(0);
            byte[] array = c.getBlob(1);
            o = Serializator.importObject(array);
        }
        closeResources(c);
        return new CacheObject(o, updated);
    }

    private void deleteCache(String type) {
        SQLiteDatabase db = dBHelper.getWritableDatabase();
        db.delete(DBHelper.CACHE_TABLE,
                DBHelper.COL_TYPE + " = ? ", 
                new String[] { type });
    }
    
    private static void closeResources(Cursor cursor) {
        if (cursor != null) {
            cursor.close();
        }
    }

}
